package Operation;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MySql {
    public void Add(String time,int money,String In_Or_Ex,String remark) throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/BillList?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("88800662");

        Connection connection = dataSource.getConnection();

        String sql = ("insert into billist value(null,?,?,?,?)");
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setNString(1,time);
        statement.setInt(2,money);
        statement.setNString(3,In_Or_Ex);
        statement.setNString(4,remark);

        int n = statement.executeUpdate();
        System.out.println("row " + n);
        statement.close();
        connection.close();
    }
    public void show () throws SQLException {
        int n = 0;//记录作用多少行
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/BillList?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("88800662");

        Connection connection = dataSource.getConnection();
        String sql = ("select * from billist");
        PreparedStatement statement = connection.prepareStatement(sql);

        ResultSet resultSet = statement.executeQuery();
        System.out.println("id     "+"时间     " + "       金额     " + "收入/支出   " + "备注");
        //判断有没有next
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String time = resultSet.getString("time");
            int money = resultSet.getInt("money");
            String In_Or_Ex = resultSet.getString("In_Or_Ex");
            String remark = resultSet.getString("remark");
            System.out.println(id + "      " + time + "     " + money + "      " + In_Or_Ex + "      " + remark);
            n++;
        }
        System.out.println("row " + n);
        resultSet.close();
        statement.close();
        connection.close();
    }
    public void delete(int id ) throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/BillList?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("88800662");

        Connection connection = dataSource.getConnection();

        String sql = ("delete from  billist where id = ?");
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,id);

        int n = statement.executeUpdate();
        System.out.println("row " + n);
        statement.close();
        connection.close();
    }
    public void update(int id,String str1,String str2) throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/BillList?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("88800662");

        Connection connection = dataSource.getConnection();
        String sql = null;
        switch (str1){
            case "时间": sql = ("update billist set time = ? where id = ?");;break;
            case "收支": sql = ("update billist set In_Or_Ex = ? where id = ?");;break;
            case  "备注": sql = ("update billist set remark = ? where id = ?");;break;
            default:
                System.out.println("输入错误");return;
        }

        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,str2);
        statement.setInt(2,id);

        int n = statement.executeUpdate();
        System.out.println("row " + n);
        statement.close();
        connection.close();

    }
    public void update(int id,int new_money) throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/BillList?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("88800662");

        Connection connection = dataSource.getConnection();

        String sql = ("update billist set money = ? where id = ?");
        PreparedStatement statement = connection.prepareStatement(sql);
       // statement.setString(1,str1);
        statement.setInt(1,new_money);
        statement.setInt(2,id);

        int n = statement.executeUpdate();
        System.out.println("row " + n);
        statement.close();
        connection.close();
    }
    public void balance() throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/BillList?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("88800662");

        Connection connection = dataSource.getConnection();

        String sql = ("select sum(money) as sum_money from billist");
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery();
        int n = 0;
        while(resultSet.next()){
            n++;
            int sum_money = resultSet.getInt("sum_money");
            System.out.println("余额:"+sum_money + "RMB");
       }
        System.out.println("row " + n);
        resultSet.close();
        statement.close();
        connection.close();
    }
    public void show_income() throws SQLException {
        int n = 0;//记录作用多少行
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/BillList?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("88800662");

        Connection connection = dataSource.getConnection();
        String sql = ("select * from billist where In_Or_Ex = '收入'");
        PreparedStatement statement = connection.prepareStatement(sql);

        ResultSet resultSet = statement.executeQuery();
        System.out.println("id     "+"时间     " + "       金额     " + "收入/支出   " + "备注");
        //判断有没有next
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String time = resultSet.getString("time");
            int money = resultSet.getInt("money");
            String In_Or_Ex = resultSet.getString("In_Or_Ex");
            String remark = resultSet.getString("remark");
            System.out.println(id + "      " + time + "     " + money + "      " + In_Or_Ex + "      " + remark);
            n++;
        }
        //查询总收入
        sql = ("select sum(money) as sum_money from billist where In_Or_Ex = '收入'");
         statement = connection.prepareStatement(sql);
         resultSet = statement.executeQuery();
        while(resultSet.next()){
            int sum_money = resultSet.getInt("sum_money");
            System.out.println("总收入:"+sum_money + "RMB");
        }
        System.out.println("row " + n);
        resultSet.close();
        statement.close();
        connection.close();
    }
    public void show_expend() throws SQLException {
        int n = 0;//记录作用多少行
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/BillList?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("88800662");

        Connection connection = dataSource.getConnection();
        String sql = ("select * from billist where In_Or_Ex = '支出'");
        PreparedStatement statement = connection.prepareStatement(sql);

        ResultSet resultSet = statement.executeQuery();
        System.out.println("id     "+"时间     " + "       金额     " + "收入/支出   " + "备注");
        //判断有没有next
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String time = resultSet.getString("time");
            int money = resultSet.getInt("money");
            String In_Or_Ex = resultSet.getString("In_Or_Ex");
            String remark = resultSet.getString("remark");
            System.out.println(id + "      " + time + "     " + money + "      " + In_Or_Ex + "      " + remark);
            n++;
        }
        //查询总支出
        sql = ("select sum(money) as sum_money from billist where In_Or_Ex = '支出'");
        statement = connection.prepareStatement(sql);
        resultSet = statement.executeQuery();
        while(resultSet.next()){
            int sum_money = resultSet.getInt("sum_money");
            System.out.println("总支出:"+sum_money + "RMB");
        }
        System.out.println("row " + n);
        resultSet.close();
        statement.close();
        connection.close();
    }
}
